import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class BookDb {
  // book.db数据库
  static const String tableBook = "book";
  static const String tableChapter = "bookChapter";
  static const String tableShelf = "bookshelf";

  final String dropTableBook = "DROP TABLE IF EXISTS $tableBook";
  final String createTableBook = """
  create table $tableBook 
  -- book表
  (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增主键
    title char(50) not null, -- 文章标题
    author char(50) not null, -- 作者
    description text, -- 文章描述
    image_url text, -- 封面图片链接
    book_url text, -- 对应html的url
    book_type text, -- 文章类型
    recommend_books text, -- 推荐阅读文章[{"title":"", "url": ""}]
    status char(10) default '连载中', -- 文章状态
    total_chapter INTEGER, -- 所有章节数
    latest_chapter char(50), -- 最新章节
    recent_read INTEGER, -- 最近阅读的章节id
    recent_update_time char(50), -- 最近一次更新时间
    create_time char(50), -- 创建时间
    update_time char(50), -- 更新时间
    unique (title, author)
  );
  """;

  final String dropTableChapter = "DROP TABLE IF EXISTS $tableChapter";
  final String createTableChapter = """
  CREATE TABLE $tableChapter 
  -- 书籍的章节
  (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增主键
    book_id TEXT, -- 所属书籍id
    chapter_id INTEGER, -- 章节号
    title TEXT, -- 章节标题
    status INTEGER default 0, -- 读取的状态0-未读,1-正在读,2-读完
    content text, -- 章节内容
    chapter_url text, -- 对应html的url
    create_time text, -- 创建时间
    update_time text, -- 更新时间
    unique (book_id, title)
    );
    """;
  final String dropTableShelf = "DROP TABLE IF EXISTS $tableShelf";
  final String createTableShelf = """
  create table $tableShelf
  -- 书架
  (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增主键
    book_id text, -- 书籍id
    book_title char(100), -- 书籍标题
    book_url char(100), -- 书籍的url
    image_url text, -- 封面图片
    create_time text, -- 创建时间
    update_time text, -- 更新时间
    unique (book_id)
  );
  """;

  Database _db;

  Future<Database> get db async {
    if (_db == null) {
      _db = await _initDb();
    }
    return _db;
  }

  _initDb() async {
    String basePath = await getDatabasesPath();
    String path = join(basePath, "bookdb.db");
    Database db = await openDatabase(path, version: 1, onCreate: _onCreate, onUpgrade: _onUpgrade);
    return db;
  }

  Future close() async {
    var result = _db.close();
    _db = null;
    return result;
  }

  void _onCreate(Database db, int newVersion) async {
    debugPrint("_onCreate newVersion:$newVersion");
    var batch = db.batch();
    batch.execute(dropTableBook);
    batch.execute(createTableBook);

    batch.execute(dropTableChapter);
    batch.execute(createTableChapter);
    
    batch.execute(dropTableShelf);
    batch.execute(createTableShelf);
    await batch.commit();
  }

  void _onUpgrade(Database db, int oldVersion, int newVersion) async {
    debugPrint("_onUpgrade oldVersion:$oldVersion");
    debugPrint("_onUpgrade newVersion:$newVersion");
    var batch = db.batch();
    await batch.commit();
  }
}

Future<Database> bookDb = BookDb().db;
